/*******************************************************************************
 * Copyright (c) 2000, 2016 IBM Corporation and others.
 *
 * This program and the accompanying materials are made
 * available under the terms of the Eclipse Public License 2.0 which accompanies this distribution, and is available at
 * https://www.eclipse.org/legal/epl-2.0/
 *
 * SPDX-License-Identifier: EPL-2.0
 *
 * Contributors: IBM Corporation - initial API and implementation
 *******************************************************************************/

package org.eclipse.test.internal.performance.db;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;

/*
 * Any SQL should only be used here.
 */
public class SQL {

    private boolean      fCompatibility = false;

    protected Connection fConnection;

    private PreparedStatement fInsertVariation, fInsertScenario, fInsertSample, fInsertDataPoint, fInsertScalar;
    private PreparedStatement fQueryComment, fInsertComment, fQueryComment2;
    private PreparedStatement fQueryVariation, fQueryVariations, fQueryScenario, fQueryAllScenarios, fQueryDatapoints,
            fQueryScalars;
    private PreparedStatement fInsertSummaryEntry, fUpdateScenarioShortName, fQuerySummaryEntry, fQueryGlobalSummaryEntries,
            fQuerySummaryEntries;
    private PreparedStatement fInsertFailure, fQueryFailure;

    protected SQL(Connection con) throws SQLException {
        fConnection = con;

        boolean needsUpgrade = true;
        boolean needsInitialization = true;
        boolean needsFailures = true;
        boolean needsComments = true;

        try (Statement statement = fConnection.createStatement()) {
            try (ResultSet rs = statement.executeQuery("select SYS.SYSTABLES.TABLENAME from SYS.SYSTABLES where SYS.SYSTABLES.TABLENAME not like 'SYS%'")) { //$NON-NLS-1$
                while (rs.next()) {
                    String tablename = rs.getString(1);
                    if ("SUMMARYENTRY".equals(tablename)) //$NON-NLS-1$
                        needsUpgrade = false;
                    else if ("CONFIG_ORG".equals(tablename)) //$NON-NLS-1$
                        fCompatibility = true;
                    else if ("VARIATION".equals(tablename)) //$NON-NLS-1$
                        needsInitialization = false;
                    else if ("FAILURE".equals(tablename)) //$NON-NLS-1$
                        needsFailures = false;
                    else if ("COMMENT".equals(tablename)) //$NON-NLS-1$
                        needsComments = false;
                }
            }
            if (!fCompatibility) {
                // check whether table SAMPLE still has the CONFIG_ID column
                try (ResultSet rs = statement.executeQuery("select count(*) from SYS.SYSTABLES, SYS.SYSCOLUMNS where SYS.SYSTABLES.TABLENAME = 'SAMPLE' and " + //$NON-NLS-1$
                        "SYS.SYSTABLES.TABLEID = SYS.SYSCOLUMNS.REFERENCEID and SYS.SYSCOLUMNS.COLUMNNAME = 'CONFIG_ID' ")) { //$NON-NLS-1$
                    if (rs.next() && rs.getInt(1) == 1)
                        fCompatibility = true;
                }
            }
        }

        if (needsInitialization)
            initialize();
        else {
            if (needsUpgrade)
                upgradeDB();
            else if (needsFailures)
                addFailureTable();
            if (needsComments)
                addCommentTable();
        }
    }

    protected void dispose() throws SQLException {
        if (fInsertVariation != null)
            fInsertVariation.close();
        if (fInsertScenario != null)
            fInsertScenario.close();
        if (fInsertSample != null)
            fInsertSample.close();
        if (fInsertDataPoint != null)
            fInsertDataPoint.close();
        if (fInsertScalar != null)
            fInsertScalar.close();
        if (fInsertSummaryEntry != null)
            fInsertSummaryEntry.close();
        if (fInsertFailure != null)
            fInsertFailure.close();
        if (fInsertComment != null)
            fInsertComment.close();
        if (fUpdateScenarioShortName != null)
            fUpdateScenarioShortName.close();
        if (fQueryDatapoints != null)
            fQueryDatapoints.close();
        if (fQueryScalars != null)
            fQueryScalars.close();
        if (fQueryVariation != null)
            fQueryVariation.close();
        if (fQueryScenario != null)
            fQueryScenario.close();
        if (fQueryAllScenarios != null)
            fQueryAllScenarios.close();
        if (fQueryVariations != null)
            fQueryVariations.close();
        if (fQueryGlobalSummaryEntries != null)
            fQueryGlobalSummaryEntries.close();
        if (fQuerySummaryEntries != null)
            fQuerySummaryEntries.close();
        if (fQueryFailure != null)
            fQueryFailure.close();
        if (fQueryComment != null)
            fQueryComment.close();
        if (fQueryComment2 != null)
            fQueryComment2.close();
    }

    private void initialize() throws SQLException {
        try  (Statement stmt = fConnection.createStatement()) {
            stmt.executeUpdate("create table VARIATION (" + //$NON-NLS-1$
                    "ID int unique not null GENERATED ALWAYS AS IDENTITY," + //$NON-NLS-1$
                    "KEYVALPAIRS varchar(10000) not null " + //$NON-NLS-1$
                    ")" //$NON-NLS-1$
            );
            stmt.executeUpdate("create table SCENARIO (" + //$NON-NLS-1$
                    "ID int unique not null GENERATED ALWAYS AS IDENTITY," + //$NON-NLS-1$
                    "NAME varchar(256) not null," + //$NON-NLS-1$
                    "SHORT_NAME varchar(40)" + //$NON-NLS-1$
                    ")" //$NON-NLS-1$
            );
            stmt.executeUpdate("create table SAMPLE (" + //$NON-NLS-1$
                    "ID int not null GENERATED ALWAYS AS IDENTITY," + //$NON-NLS-1$
                    "VARIATION_ID int not null," + //$NON-NLS-1$
                    "SCENARIO_ID int not null," + //$NON-NLS-1$
                    "STARTTIME timestamp" + //$NON-NLS-1$
                    ")" //$NON-NLS-1$
            );
            stmt.executeUpdate("create table DATAPOINT (" + //$NON-NLS-1$
                    "ID int not null GENERATED ALWAYS AS IDENTITY," + //$NON-NLS-1$
                    "SAMPLE_ID int not null," + //$NON-NLS-1$
                    "SEQ int," + //$NON-NLS-1$
                    "STEP int" + //$NON-NLS-1$
                    ")" //$NON-NLS-1$
            );
            stmt.executeUpdate("create table SCALAR (" + //$NON-NLS-1$
                    "DATAPOINT_ID int not null," + //$NON-NLS-1$
                    "DIM_ID int not null," + //$NON-NLS-1$
                    "VALUE bigint" + //$NON-NLS-1$
                    ")" //$NON-NLS-1$
            );
            stmt.executeUpdate("create table SUMMARYENTRY (" + //$NON-NLS-1$
                    "VARIATION_ID int not null," + //$NON-NLS-1$
                    "SCENARIO_ID int not null," + //$NON-NLS-1$
                    "DIM_ID int not null," + //$NON-NLS-1$
                    "IS_GLOBAL smallint not null," + //$NON-NLS-1$
                    "COMMENT_ID int not null" + //$NON-NLS-1$
                    ")" //$NON-NLS-1$
            );
            stmt.executeUpdate("create table FAILURE (" + //$NON-NLS-1$
                    "VARIATION_ID int not null," + //$NON-NLS-1$
                    "SCENARIO_ID int not null," + //$NON-NLS-1$
                    "MESSAGE varchar(1000) not null" + //$NON-NLS-1$
                    ")" //$NON-NLS-1$
            );
            stmt.executeUpdate("create table COMMENT (" + //$NON-NLS-1$
                    "ID int unique not null GENERATED ALWAYS AS IDENTITY," + //$NON-NLS-1$
                    "KIND int not null," + //$NON-NLS-1$
                    "TEXT varchar(400) not null" + //$NON-NLS-1$
                    ")" //$NON-NLS-1$
            );

            // Primary/unique
            stmt.executeUpdate("alter table VARIATION add constraint VA_KVP primary key (KEYVALPAIRS)"); //$NON-NLS-1$
            stmt.executeUpdate("alter table SCENARIO add constraint SC_NAME primary key (NAME)"); //$NON-NLS-1$
            stmt.executeUpdate("alter table SAMPLE add constraint SA_ID primary key (ID)"); //$NON-NLS-1$
            stmt.executeUpdate("alter table DATAPOINT add constraint DP_ID primary key (ID)"); //$NON-NLS-1$

            // Foreign
            stmt.executeUpdate("alter table SAMPLE add constraint SAMPLE_CONSTRAINT " + //$NON-NLS-1$
                    "foreign key (VARIATION_ID) references VARIATION (ID)"); //$NON-NLS-1$
            stmt.executeUpdate("alter table SAMPLE add constraint SAMPLE_CONSTRAINT2 " + //$NON-NLS-1$
                    "foreign key (SCENARIO_ID) references SCENARIO (ID)"); //$NON-NLS-1$
            stmt.executeUpdate("alter table DATAPOINT add constraint DP_CONSTRAINT " + //$NON-NLS-1$
                    "foreign key (SAMPLE_ID) references SAMPLE (ID)"); //$NON-NLS-1$
            stmt.executeUpdate("alter table SCALAR add constraint SCALAR_CONSTRAINT " + //$NON-NLS-1$
                    "foreign key (DATAPOINT_ID) references DATAPOINT (ID)"); //$NON-NLS-1$

            stmt.executeUpdate("alter table SUMMARYENTRY add constraint FP_CONSTRAINT " + //$NON-NLS-1$
                    "foreign key (VARIATION_ID) references VARIATION (ID)"); //$NON-NLS-1$
            stmt.executeUpdate("alter table SUMMARYENTRY add constraint FP_CONSTRAINT2 " + //$NON-NLS-1$
                    "foreign key (SCENARIO_ID) references SCENARIO (ID)"); //$NON-NLS-1$

            stmt.executeUpdate("alter table FAILURE add constraint FA_CONSTRAINT " + //$NON-NLS-1$
                    "foreign key (VARIATION_ID) references VARIATION (ID)"); //$NON-NLS-1$
            stmt.executeUpdate("alter table FAILURE add constraint FA_CONSTRAINT2 " + //$NON-NLS-1$
                    "foreign key (SCENARIO_ID) references SCENARIO (ID)"); //$NON-NLS-1$

            fConnection.commit();

        }
    }

    private void upgradeDB() throws SQLException {
        try (Statement stmt = fConnection.createStatement();){
            stmt.executeUpdate("create table SUMMARYENTRY (" + //$NON-NLS-1$
                    "VARIATION_ID int not null," + //$NON-NLS-1$
                    "SCENARIO_ID int not null," + //$NON-NLS-1$
                    "DIM_ID int not null," + //$NON-NLS-1$
                    "IS_GLOBAL smallint not null," + //$NON-NLS-1$
                    "COMMENT_ID int not null" + //$NON-NLS-1$
                    ")" //$NON-NLS-1$
            );
            stmt.executeUpdate("alter table SUMMARYENTRY add constraint FP_CONSTRAINT " + //$NON-NLS-1$
                    "foreign key (VARIATION_ID) references VARIATION (ID)"); //$NON-NLS-1$
            stmt.executeUpdate("alter table SUMMARYENTRY add constraint FP_CONSTRAINT2 " + //$NON-NLS-1$
                    "foreign key (SCENARIO_ID) references SCENARIO (ID)"); //$NON-NLS-1$

            stmt.executeUpdate("alter table SCENARIO add column SHORT_NAME varchar(40)"); //$NON-NLS-1$

            fConnection.commit();

        }
    }

    private void addCommentTable() throws SQLException {
        try (Statement stmt = fConnection.createStatement()){
            stmt.executeUpdate("create table COMMENT (" + //$NON-NLS-1$
                    "ID int unique not null GENERATED ALWAYS AS IDENTITY," + //$NON-NLS-1$
                    "KIND int not null," + //$NON-NLS-1$
                    "TEXT varchar(400) not null" + //$NON-NLS-1$
                    ")" //$NON-NLS-1$
            );

            stmt.executeUpdate("alter table SUMMARYENTRY add column COMMENT_ID int not null default 0"); //$NON-NLS-1$

            fConnection.commit();

        }
    }

    private void addFailureTable() throws SQLException {
        try (Statement stmt = fConnection.createStatement();){
            stmt.executeUpdate("create table FAILURE (" + //$NON-NLS-1$
                    "VARIATION_ID int not null," + //$NON-NLS-1$
                    "SCENARIO_ID int not null," + //$NON-NLS-1$
                    "MESSAGE varchar(1000) not null" + //$NON-NLS-1$
                    ")" //$NON-NLS-1$
            );

            stmt.executeUpdate("alter table FAILURE add constraint FA_CONSTRAINT " + //$NON-NLS-1$
                    "foreign key (VARIATION_ID) references VARIATION (ID)"); //$NON-NLS-1$
            stmt.executeUpdate("alter table FAILURE add constraint FA_CONSTRAINT2 " + //$NON-NLS-1$
                    "foreign key (SCENARIO_ID) references SCENARIO (ID)"); //$NON-NLS-1$

            fConnection.commit();

        }
    }

    static int create(PreparedStatement stmt) throws SQLException {
        stmt.executeUpdate();
        try (ResultSet rs = stmt.getGeneratedKeys()) {
            if (rs != null) {
                if (rs.next()) {
                    BigDecimal idColVar = rs.getBigDecimal(1);
                    return idColVar.intValue();
                }
            }
        }
        return 0;
    }

    int getScenario(String scenarioPattern) throws SQLException {
        if (fQueryScenario == null)
            fQueryScenario = fConnection.prepareStatement("select ID from SCENARIO where NAME = ?"); //$NON-NLS-1$
        fQueryScenario.setString(1, scenarioPattern);
        try (ResultSet result = fQueryScenario.executeQuery()) {
            while (result.next())
                return result.getInt(1);
        }
        if (fInsertScenario == null)
            fInsertScenario = fConnection.prepareStatement(
                    "insert into SCENARIO (NAME) values (?)", Statement.RETURN_GENERATED_KEYS); //$NON-NLS-1$
        fInsertScenario.setString(1, scenarioPattern);
        return create(fInsertScenario);
    }

    int getVariations(Variations variations) throws SQLException {
        if (fQueryVariation == null)
            fQueryVariation = fConnection.prepareStatement("select ID from VARIATION where KEYVALPAIRS = ?"); //$NON-NLS-1$
        String exactMatchString = variations.toExactMatchString();
        fQueryVariation.setString(1, exactMatchString);
        try (ResultSet result = fQueryVariation.executeQuery()) {
            while (result.next())
                return result.getInt(1);
        }
        if (fInsertVariation == null)
            fInsertVariation = fConnection.prepareStatement(
                    "insert into VARIATION (KEYVALPAIRS) values (?)", Statement.RETURN_GENERATED_KEYS); //$NON-NLS-1$
        fInsertVariation.setString(1, exactMatchString);
        return create(fInsertVariation);
    }

    int createSample(int variation_id, int scenario_id, Timestamp starttime) throws SQLException {
        if (fInsertSample == null) {
            if (fCompatibility) {
                // since we cannot remove table columns in cloudscape we have to
                // provide a non-null value for CONFIG_ID
                fInsertSample = fConnection
                        .prepareStatement(
                                "insert into SAMPLE (VARIATION_ID, SCENARIO_ID, STARTTIME, CONFIG_ID) values (?, ?, ?, 0)", Statement.RETURN_GENERATED_KEYS); //$NON-NLS-1$
            } else {
                fInsertSample = fConnection
                        .prepareStatement(
                                "insert into SAMPLE (VARIATION_ID, SCENARIO_ID, STARTTIME) values (?, ?, ?)", Statement.RETURN_GENERATED_KEYS); //$NON-NLS-1$
            }
        }
        fInsertSample.setInt(1, variation_id);
        fInsertSample.setInt(2, scenario_id);
        fInsertSample.setTimestamp(3, starttime);
        return create(fInsertSample);
    }

    int createDataPoint(int sample_id, int seq, int step) throws SQLException {
        if (fInsertDataPoint == null)
            fInsertDataPoint = fConnection.prepareStatement(
                    "insert into DATAPOINT (SAMPLE_ID, SEQ, STEP) values (?, ?, ?)", Statement.RETURN_GENERATED_KEYS); //$NON-NLS-1$
        fInsertDataPoint.setInt(1, sample_id);
        fInsertDataPoint.setInt(2, seq);
        fInsertDataPoint.setInt(3, step);
        return create(fInsertDataPoint);
    }

    void insertScalar(int datapoint_id, int dim_id, long value) throws SQLException {
        if (fInsertScalar == null)
            fInsertScalar = fConnection.prepareStatement("insert into SCALAR values (?, ?, ?)"); //$NON-NLS-1$
        fInsertScalar.setInt(1, datapoint_id);
        fInsertScalar.setInt(2, dim_id);
        fInsertScalar.setLong(3, value);
        fInsertScalar.executeUpdate();
    }

    ResultSet queryDataPoints(Variations variations, String scenarioName) throws SQLException {
        if (fQueryDatapoints == null)
            fQueryDatapoints = fConnection
                    .prepareStatement("select DATAPOINT.ID, DATAPOINT.STEP from VARIATION, SCENARIO, SAMPLE, DATAPOINT " + //$NON-NLS-1$
                            "where " + //$NON-NLS-1$
                            "SAMPLE.VARIATION_ID = VARIATION.ID and VARIATION.KEYVALPAIRS = ? and " + //$NON-NLS-1$
                            "SAMPLE.SCENARIO_ID = SCENARIO.ID and SCENARIO.NAME LIKE ? and " + //$NON-NLS-1$
                            "DATAPOINT.SAMPLE_ID = SAMPLE.ID " //$NON-NLS-1$
                    );
        fQueryDatapoints.setString(1, variations.toExactMatchString());
        fQueryDatapoints.setString(2, scenarioName);
        return fQueryDatapoints.executeQuery();
    }

    ResultSet queryScalars(int datapointId) throws SQLException {
        if (fQueryScalars == null)
            fQueryScalars = fConnection
                    .prepareStatement("select SCALAR.DIM_ID, SCALAR.VALUE from SCALAR where SCALAR.DATAPOINT_ID = ?"); //$NON-NLS-1$
        fQueryScalars.setInt(1, datapointId);
        return fQueryScalars.executeQuery();
    }

    /*
     * Returns SCENARIO.NAME
     */
    ResultSet queryScenarios(Variations variations, String scenarioPattern) throws SQLException {
        if (fQueryAllScenarios == null)
            fQueryAllScenarios = fConnection
                    .prepareStatement("select distinct SCENARIO.NAME from SCENARIO, SAMPLE, VARIATION where " + //$NON-NLS-1$
                            "SAMPLE.VARIATION_ID = VARIATION.ID and VARIATION.KEYVALPAIRS LIKE ? and " + //$NON-NLS-1$
                            "SAMPLE.SCENARIO_ID = SCENARIO.ID and SCENARIO.NAME LIKE ?" //$NON-NLS-1$
                    );
        fQueryAllScenarios.setString(1, variations.toQueryPattern());
        fQueryAllScenarios.setString(2, scenarioPattern);
        return fQueryAllScenarios.executeQuery();
    }

    /*
     * Returns VARIATION.KEYVALPAIRS
     */
    ResultSet queryVariations(String variations, String scenarioPattern) throws SQLException {
        if (fQueryVariations == null)
            fQueryVariations = fConnection
                    .prepareStatement("select distinct VARIATION.KEYVALPAIRS from VARIATION, SAMPLE, SCENARIO where " + //$NON-NLS-1$
                            "SAMPLE.VARIATION_ID = VARIATION.ID and VARIATION.KEYVALPAIRS LIKE ? and " + //$NON-NLS-1$
                            "SAMPLE.SCENARIO_ID = SCENARIO.ID and SCENARIO.NAME LIKE ?" //$NON-NLS-1$
                    );
        fQueryVariations.setString(1, variations);
        fQueryVariations.setString(2, scenarioPattern);
        return fQueryVariations.executeQuery();
    }

    void createSummaryEntry(int variation_id, int scenario_id, int dim_id, boolean isGlobal, int comment_id) throws SQLException {
        if (fQuerySummaryEntry == null)
            fQuerySummaryEntry = fConnection
                    .prepareStatement("select count(*) from SUMMARYENTRY where VARIATION_ID = ? and SCENARIO_ID = ? and DIM_ID = ? and IS_GLOBAL = ? and COMMENT_ID = ?"); //$NON-NLS-1$
        fQuerySummaryEntry.setInt(1, variation_id);
        fQuerySummaryEntry.setInt(2, scenario_id);
        fQuerySummaryEntry.setInt(3, dim_id);
        fQuerySummaryEntry.setShort(4, (short) (isGlobal ? 1 : 0));
        fQuerySummaryEntry.setInt(5, comment_id);
        try (ResultSet result = fQuerySummaryEntry.executeQuery()) {
            if (result.next() && result.getInt(1) > 0)
                return;
        }
        if (fInsertSummaryEntry == null)
            fInsertSummaryEntry = fConnection
                    .prepareStatement("insert into SUMMARYENTRY (VARIATION_ID, SCENARIO_ID, DIM_ID, IS_GLOBAL, COMMENT_ID) values (?, ?, ?, ?, ?)"); //$NON-NLS-1$
        fInsertSummaryEntry.setInt(1, variation_id);
        fInsertSummaryEntry.setInt(2, scenario_id);
        fInsertSummaryEntry.setInt(3, dim_id);
        fInsertSummaryEntry.setShort(4, (short) (isGlobal ? 1 : 0));
        fInsertSummaryEntry.setInt(5, comment_id);
        fInsertSummaryEntry.executeUpdate();
    }

    public void setScenarioShortName(int scenario_id, String shortName) throws SQLException {
        if (shortName.length() >= 40)
            shortName = shortName.substring(0, 40);
        if (fUpdateScenarioShortName == null)
            fUpdateScenarioShortName = fConnection.prepareStatement("update SCENARIO set SHORT_NAME = ? where SCENARIO.ID = ?"); //$NON-NLS-1$
        fUpdateScenarioShortName.setString(1, shortName);
        fUpdateScenarioShortName.setInt(2, scenario_id);
        fUpdateScenarioShortName.executeUpdate();
    }

    ResultSet queryGlobalSummaryEntries(Variations variations) throws SQLException {
        if (fQueryGlobalSummaryEntries == null)
            fQueryGlobalSummaryEntries = fConnection
                    .prepareStatement("select distinct SCENARIO.NAME, SCENARIO.SHORT_NAME, SUMMARYENTRY.DIM_ID, SUMMARYENTRY.IS_GLOBAL, SUMMARYENTRY.COMMENT_ID " + //$NON-NLS-1$
                            "from VARIATION, SCENARIO, SUMMARYENTRY " + //$NON-NLS-1$
                            "where SUMMARYENTRY.VARIATION_ID = VARIATION.ID " + //$NON-NLS-1$
                            "and VARIATION.KEYVALPAIRS LIKE ? " + //$NON-NLS-1$
                            "and SUMMARYENTRY.SCENARIO_ID = SCENARIO.ID " + //$NON-NLS-1$
                            "and SUMMARYENTRY.IS_GLOBAL = 1 " + //$NON-NLS-1$
                            "order by SCENARIO.NAME" //$NON-NLS-1$
                    );
        fQueryGlobalSummaryEntries.setString(1, variations.toExactMatchString());
        return fQueryGlobalSummaryEntries.executeQuery();
    }

    ResultSet querySummaryEntries(Variations variations, String scenarioPattern) throws SQLException {
        if (fQuerySummaryEntries == null)
            fQuerySummaryEntries = fConnection
                    .prepareStatement("select distinct SCENARIO.NAME, SCENARIO.SHORT_NAME, SUMMARYENTRY.DIM_ID, SUMMARYENTRY.IS_GLOBAL, SUMMARYENTRY.COMMENT_ID " + //$NON-NLS-1$
                            "from VARIATION, SCENARIO, SUMMARYENTRY " + //$NON-NLS-1$
                            "where SUMMARYENTRY.VARIATION_ID = VARIATION.ID " + //$NON-NLS-1$
                            "and VARIATION.KEYVALPAIRS LIKE ? " + //$NON-NLS-1$
                            "and SUMMARYENTRY.SCENARIO_ID = SCENARIO.ID " + //$NON-NLS-1$
                            "and SCENARIO.NAME like ? " + //$NON-NLS-1$
                            "order by SCENARIO.NAME" //$NON-NLS-1$
                    );
        fQuerySummaryEntries.setString(1, variations.toExactMatchString());
        fQuerySummaryEntries.setString(2, scenarioPattern);
        return fQuerySummaryEntries.executeQuery();
    }

    void insertFailure(int variation_id, int scenario_id, String message) throws SQLException {
        if (fInsertFailure == null)
            fInsertFailure = fConnection.prepareStatement("insert into FAILURE values (?, ?, ?)"); //$NON-NLS-1$
        fInsertFailure.setInt(1, variation_id);
        fInsertFailure.setInt(2, scenario_id);
        fInsertFailure.setString(3, message);
        fInsertFailure.executeUpdate();
    }

    public ResultSet queryFailure(Variations variations, String scenarioPattern) throws SQLException {
        if (fQueryFailure == null)
            fQueryFailure = fConnection
                    .prepareStatement("select SCENARIO.NAME, FAILURE.MESSAGE from FAILURE, VARIATION, SCENARIO where " + //$NON-NLS-1$
                            "FAILURE.VARIATION_ID = VARIATION.ID and VARIATION.KEYVALPAIRS LIKE ? and " + //$NON-NLS-1$
                            "FAILURE.SCENARIO_ID = SCENARIO.ID and SCENARIO.NAME LIKE ?" //$NON-NLS-1$
                    );
        fQueryFailure.setString(1, variations.toExactMatchString());
        fQueryFailure.setString(2, scenarioPattern);
        return fQueryFailure.executeQuery();
    }

    int getCommentId(int commentKind, String comment) throws SQLException {
        if (comment.length() > 400)
            comment = comment.substring(0, 400);
        if (fQueryComment == null)
            fQueryComment = fConnection.prepareStatement("select ID from COMMENT where KIND = ? and TEXT = ?"); //$NON-NLS-1$
        fQueryComment.setInt(1, commentKind);
        fQueryComment.setString(2, comment);
        try (ResultSet result = fQueryComment.executeQuery()) {
            while (result.next())
                return result.getInt(1);
        }
        if (fInsertComment == null)
            fInsertComment = fConnection.prepareStatement(
                    "insert into COMMENT (KIND, TEXT) values (?, ?)", Statement.RETURN_GENERATED_KEYS); //$NON-NLS-1$
        fInsertComment.setInt(1, commentKind);
        fInsertComment.setString(2, comment);
        return create(fInsertComment);
    }

    public ResultSet getComment(int comment_id) throws SQLException {
        if (fQueryComment2 == null)
            fQueryComment2 = fConnection.prepareStatement("select KIND, TEXT from COMMENT where ID = ?"); //$NON-NLS-1$
        fQueryComment2.setInt(1, comment_id);
        return fQueryComment2.executeQuery();
    }
}
